#    Copyright 2013 Tri-County Electric Cooperative, St. Matthews, South Carolina
#    This file is part of COOPIP.
#    COOPIP is free software: you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation, either version 3 of the License, or
#    (at your option) any later version.
#    COOPIP is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#    You should have received a copy of the GNU General Public License
#    along with COOPIP.  If not, see <http://www.gnu.org/licenses/>.


import sys
NeededPaths=['/opt/coopip/djangoproject/include', '/opt/coopip/djangoproject', '/opt/cis', '/opt/oms/', '/opt/amr', '/opt/gis','/opt/gos', '/opt']
for path in NeededPaths:
    if(not path in sys.path):
        sys.path.append(path)
import httplib
import os, csv, shutil, datetime, time, reportlab, djangoproject.settings, datetime
from django.contrib.gis.db import models
from django.contrib.gis.geos import *
from django.http import HttpResponse
from django.contrib.gis import admin
import _mysql as mysql
from django.db import transaction
import django
import COOPIP_Form, COOPIP_Misc
import CIS, AMR, GIS, GOS  #Comment this out before doing a syncdb
import reportlab, reportlab.lib, reportlab.graphics.shapes
import Image
from reportlab.graphics import renderPDF
import reportlab.platypus
    

def CreateRevenueProtectionReportPDF(Report):
    styles=reportlab.lib.styles.getSampleStyleSheet()
    doc=reportlab.platypus.SimpleDocTemplate(Report.FileName)
    doc.pagesize=reportlab.lib.pagesizes.landscape(reportlab.lib.pagesizes.LEGAL)
    doc.leftMargin = 0.27 * reportlab.lib.units.inch
    doc.rightMargin=0.27 * reportlab.lib.units.inch
    doc.topMargin=0.27 * reportlab.lib.units.inch
    doc.bottomMargin=0.27 * reportlab.lib.units.inch
    Story=[]
    Debug=False  #Enable Debug Mode, with smaller reports for ones that take a long time to run
    ActiveButNotAuthorizedMeters = True #used for debugging
    RecentDisconnects = True #used for debugging
    DailyReverseRotation = True #used for debugging
    MeterBlinks = True #used for debugging
    MeterUsageCharts = True  #used for debugging
    Now = datetime.datetime.now()
    DateTimeString = str(Now.year) + '-' + str(Now.month) + '-' + str(Now.day)
    #Active but not Authorized Meters With Usage Chart*********************************************************
    if(ActiveButNotAuthorizedMeters):
        Story.append(reportlab.platypus.Paragraph('Active But Not Authorized Meters ' + DateTimeString, styles['Heading3']))
        ActiveButNotAuthorizedMetersFlowables=CreateActiveButNotAuthorizedMeterFlowables(Debug)
        Story.append(ActiveButNotAuthorizedMetersFlowables)
    #End Active but not Authorized Meters With Usage Chart*********************************************************
    
    #RecentDisconnects Chart*********************************************************
    if(RecentDisconnects):
        Story.append(reportlab.platypus.Paragraph('Recent Disconnects '+ DateTimeString, styles['Heading3']))
        RecentDisconnectsFlowables=CreateRecentDisconnectsFlowables(Debug)
        Story.append(RecentDisconnectsFlowables)
    #End SevenDayDisconnects Chart*********************************************************

    #Append Reverse Rotations Chart*********************************************************
    if(DailyReverseRotation):
        Story.append(reportlab.platypus.Paragraph('Reverse Rotations ' + DateTimeString, styles['Heading3']))
        ReverseRotationFlowableHourly, ReverseRotationFlowableDaily=CreateReverseRotationFlowables(Debug)
        Story.append(reportlab.platypus.Paragraph('Reverse Rotations Hourly ' + DateTimeString, styles['Heading4']))
        Story.append(ReverseRotationFlowableHourly)
        Story.append(reportlab.platypus.Paragraph('Reverse Rotations Daily ' + DateTimeString, styles['Heading4']))
        Story.append(ReverseRotationFlowableDaily)

    #End Reverse Rotations Section*********************************************************



    #Append Meter Blink Charts*********************************************************
    if(MeterBlinks):
        Story.append(reportlab.platypus.Paragraph('Recent Meter Blinks.  Last AMR Meter Blink Reset was:  3/15/2013.  This Report Ran on:  ' + DateTimeString, styles['Heading3']))
        MeterBlinkFlowables=CreateMeterBlinkFlowables(Debug)
        Story.append(MeterBlinkFlowables)
    #End Meter Blink Charts Section*********************************************************
    
    #Append Daily Meter Usage Charts to the Story*********************************************************
    if(MeterUsageCharts):
        Story.append(reportlab.platypus.Paragraph('Meter Usage Charts:  ' + DateTimeString, styles['Heading3']))
        Story.append(reportlab.platypus.Paragraph('Red:  High Consumption, Yellow:  Moderate Consumption, Green:  Low Consumption, Black:  No Reading Available  ' + DateTimeString, styles['Heading4']))
        MeterUsageCharts=CreateMeterUsageCharts(Debug)
        MeterUsageChartsTable=CreateMeterUsageChartsTable(MeterUsageCharts, Columns=4)
        Story.append(MeterUsageChartsTable)
    #End Meter Usage Chart Section*********************************************************

    try:
        os.remove(Report.FileName)
    except:
        pass
    doc.build(Story)


#Start MeterBlinkFlowables Section*********************************************************
def CreateMeterBlinkFlowables(Debug):
    styles=reportlab.lib.styles.getSampleStyleSheet()
    RecentDates=djangoproject.models.ipbase.MeterBlink().GetMostRecentDates()
    TableHeadingList=[reportlab.platypus.Paragraph('Serial Number', styles['Normal']), reportlab.platypus.Paragraph('Blinks', styles['Normal']), reportlab.platypus.Paragraph('Alone', styles['Normal']), 
                      reportlab.platypus.Paragraph('In Row', styles['Normal']), reportlab.platypus.Paragraph('Circuit', styles['Normal'])]
    for i in RecentDates:
        TableHeadingList.append(reportlab.platypus.Paragraph(i, styles['Normal']))
    TableHeadingList.extend([reportlab.platypus.Paragraph('S/O Date', styles['Normal']), 
                             reportlab.platypus.Paragraph('S/O Description', styles['Normal']), reportlab.platypus.Paragraph('Notes', styles['Normal'])])
    #This report runs quickly, so Debug is ignored
    MeterBlinks=list(djangoproject.models.ipbase.MeterBlink.objects.filter(BlinkCountTimeSpan__gte=1).select_related('Meter').order_by('Meter__GISCircuitNumber'))
    Results = []
    Results.append(TableHeadingList)
    for meterBlink in MeterBlinks:
        row = []
        RawCounts = meterBlink.GetSpecifiedRawCounts(RecentDates, BlankDatesWithPrevious=True)
        row.append(reportlab.platypus.Paragraph(str(meterBlink.Meter.SerialNumber) + ' ' + str(meterBlink.Meter.RevenueProtectionReportNote), styles['Normal']))
        row.append(reportlab.platypus.Paragraph(str(meterBlink.BlinkCountTimeSpan), styles['Normal']))
        row.append(reportlab.platypus.Paragraph(str(meterBlink.BlinkWithNoNeighborTimeSpan), styles['Normal']))
        row.append(reportlab.platypus.Paragraph(str(meterBlink.BlinkDaysInRowTimeSpan), styles['Normal']))
        row.append(reportlab.platypus.Paragraph(str(meterBlink.Meter.GISCircuitNumber), styles['Normal']))
        count = 0
        for count in range(0, len(RecentDates) - 1):
            row.append(reportlab.platypus.Paragraph(str(RawCounts[count]), styles['Normal']))
            

        #FIXME Add more statistics/days here
        Results.append(row)
    return reportlab.platypus.Table(Results)    
        


#End MeterBlinkFlowables Section *********************************************************



#Start MeterUsageCharts Section*********************************************************
def GetKWHNetUsage(DateKeys, meter):
    MeterRead = djangoproject.models.ipbase.MeterRead.objects.filter(Meter=meter)
    if(not MeterRead):
        return [None] * len(DateKeys)
    ResultsList = []
    MeterReadDict=MeterRead[0].GetKWHUsageDict()
    for key in DateKeys:
        if(MeterReadDict.has_key(key)):
            ResultsList.append(MeterReadDict[key])
        else:
            ResultsList.append(None)
    return ResultsList


def GetHourlyNetReading(DateKeys, meter):
    MeterRead = djangoproject.models.ipbase.MeterRead.objects.filter(Meter=meter)
    if(not MeterRead):
        return None
    ResultsList = []
    MeterReadDict=MeterRead[0].GetHourlyReadingNetDict()
    NoValuesFound=True
    for key in DateKeys:
        if(MeterReadDict.has_key(key)):
            ResultsList.append(MeterReadDict[key])
            NoValuesFound = False
        else:
            ResultsList.append([None] * 24)
    if(NoValuesFound):
        return None
    return ResultsList

def GetDateKeys(NumberOfDays):
    StartDateTime=datetime.datetime.now() - datetime.timedelta(days=1)
    CurrentDateTime = StartDateTime
    KeyList=[]
    while(len(KeyList) < NumberOfDays):
        KeyList.append(djangoproject.models.ipbase.MeterRead().DateTimeToKey(CurrentDateTime))
        CurrentDateTime = CurrentDateTime - datetime.timedelta(days=1)
    KeyList.reverse()
    return KeyList



def CreateMeterUsageCharts(Debug):
    FlowablesList=[]
    styles=reportlab.lib.styles.getSampleStyleSheet()
    if(Debug):
       Meters=djangoproject.models.ipbase.Meter.objects.all().order_by('SerialNumber')[:50]
    else:
       Meters=djangoproject.models.ipbase.Meter.objects.all().order_by('SerialNumber')
 
    Days = 9
    DateKeys=GetDateKeys(Days + 1)[:Days - 1]
    for meter in Meters:
        HourlyUsageList = GetHourlyNetReading(DateKeys, meter)
        if(not HourlyUsageList):
            continue
        MeterUsageChart = reportlab.graphics.shapes.Drawing(225, 250)
        MeterUsageChart.add(reportlab.graphics.shapes.String(25, 10, str(meter.SerialNumber), fontSize=12, fillColor=reportlab.lib.colors.black))
        MeterUsageChart.add(reportlab.graphics.shapes.Rect(25, 35, 200, 175, fillColor=reportlab.lib.colors.white))
        MeterUsageChart.add(reportlab.graphics.shapes.String(5, 1, str(meter.RevenueProtectionReportNote), fontSize=8, fillColor=reportlab.lib.colors.black))
        MeterUsageChart.add(reportlab.graphics.shapes.String(30, 215, str(meter.CISAccountName), fontSize=8, fillColor=reportlab.lib.colors.black))
        day = 0
        for day in range(0, Days - 1):
            hour = 0
            for hour in range(0, 23):
                x1 = 25 + 1 * ((day * 24) + hour)
                x2 = 25 + 1 * ((day * 24) + hour)
                y1 = 35
                if(type(HourlyUsageList[day][hour]) not in [int, float]):

                    y2 = 35 + 100
                    MeterUsageChart.add(reportlab.graphics.shapes.Line(x1, y1, x2, y2, strokeColor=reportlab.lib.colors.black))
                else:
                    if(HourlyUsageList[day][hour] > 5000):
                        strokeColor=reportlab.lib.colors.red
                        Divisor = 200.0
                    elif(HourlyUsageList[day][hour] > 500):
                        strokeColor=reportlab.lib.colors.orange
                        Divisor = 20.0
                    elif(HourlyUsageList[day][hour] > 100):
                        strokeColor=reportlab.lib.colors.green
                        Divisor = 3.0
                    else:
                        strokeColor=reportlab.lib.colors.blue
                        Divisor = 0.5

                    y2 = 35 + int(HourlyUsageList[day][hour]/Divisor)

                    MeterUsageChart.add(reportlab.graphics.shapes.Line(x1,y1,x2,y2 , strokeColor=strokeColor))
        FlowablesList.append(MeterUsageChart)
        #FlowablesList.append(reportlab.platypus.Spacer(0, 0.05*reportlab.lib.units.inch))
    return FlowablesList


def CreateMeterUsageChartsTable(MeterUsageCharts, Columns=4):
    Row = []  
    Count = 0
    MeterUsageChartsTableData=[]
    for i in MeterUsageCharts:
        Row.append(i)
        Count = Count + 1
        if(Count == Columns):
            Count = 0
            MeterUsageChartsTableData.append(Row)
            Row = []
    if(Row):
        MeterUsageChartsTableData.append(Row)
    MeterUsageChartsTable=reportlab.platypus.Table(MeterUsageChartsTableData)
    return MeterUsageChartsTable
#End MeterUsageChartsSection*********************************************************




def CreateActiveButNotAuthorizedMeterFlowables(Debug):
    styles=reportlab.lib.styles.getSampleStyleSheet()
    TableHeadingList=[reportlab.platypus.Paragraph('Meter #', styles['Normal']), reportlab.platypus.Paragraph('Disconnected', styles['Normal']), reportlab.platypus.Paragraph('Lastest Read Date', styles['Normal']), 
                      reportlab.platypus.Paragraph('KWH', styles['Normal']), reportlab.platypus.Paragraph('Previous Read Date', styles['Normal']), reportlab.platypus.Paragraph('KWH', styles['Normal'])]

    MeterBlinks=list(djangoproject.models.ipbase.MeterBlink.objects.filter(BlinkCountTimeSpan__gte=1).select_related('Meter').order_by('Meter__GISCircuitNumber')[:50])
    Results = []
    Results.append(TableHeadingList)
    #Debug is ignored as thie report runs quickly
    ActiveButNotAuthorizedMeters=djangoproject.models.ipbase.Meter.objects.filter(Symbology='activebutnotauthorized').filter(CISIdleIndicator=True).order_by('SerialNumber')


    for meter in ActiveButNotAuthorizedMeters:
        row=[]
        row.append(reportlab.platypus.Paragraph(meter.SerialNumber + ' ' + meter.RevenueProtectionReportNote, styles['Normal']))
        DisconnectHistory=CIS.Locationdetl.objects.filter(meter=meter.SerialNumber.rjust(12,'0')).order_by('-discdate')
        if not DisconnectHistory.count():
            continue
        LastDisconnect=COOPIP_Misc.ConvertCISDisconnectDate(DisconnectHistory[0].discdate)
        row.append(reportlab.platypus.Paragraph(LastDisconnect.strftime('%m/%d/%Y'), styles['Normal']))
        Read1Date, Read1KWH, Read2Date, Read2KWH = meter.GetLastTwoReads()
        AMRMeterHistory=AMR.Meteracctstc.objects.filter(serialnumber=meter.SerialNumber).order_by('-metertcreaddt')[:2]
        row.append(reportlab.platypus.Paragraph(Read1Date, styles['Normal']))
        row.append(reportlab.platypus.Paragraph(str(Read1KWH), styles['Normal']))
        row.append(reportlab.platypus.Paragraph(Read2Date, styles['Normal']))
        row.append(reportlab.platypus.Paragraph(str(Read2KWH), styles['Normal']))
        Results.append(row)

    return reportlab.platypus.Table(Results)    






def CreateReverseRotationFlowables(Debug):
    NumberOfDays = 10
    StartDate = datetime.datetime.now() - datetime.timedelta(days=NumberOfDays)
    RecentDates=djangoproject.models.ipbase.MeterBlink().GetMostRecentDates(NumberOfDates=NumberOfDays)


    styles=reportlab.lib.styles.getSampleStyleSheet()
    DailyTableHeadingList=[reportlab.platypus.Paragraph('Meter #', styles['Normal'])]
    for date in RecentDates:
        DailyTableHeadingList.append(reportlab.platypus.Paragraph(date, styles['Normal']))
    DailyTableList=[DailyTableHeadingList]

    HourlyTableHeadingList=[reportlab.platypus.Paragraph('Meter #', styles['Normal']), reportlab.platypus.Paragraph('Date', styles['Normal'])]
    for i in range(0, 24):
         HourlyTableHeadingList.append(reportlab.platypus.Paragraph(str(i), styles['Normal']))
    HourlyTableList=[HourlyTableHeadingList]
    if(Debug):
        Meters=list(djangoproject.models.ipbase.Meter.objects.filter(LastSuccessfulPingDateTime__gte=StartDate).filter(SerialNumber__in=['55176841', '55179071', '82778455']).order_by('SerialNumber'))
    else:
        Meters=list(djangoproject.models.ipbase.Meter.objects.filter(LastSuccessfulPingDateTime__gte=StartDate).order_by('SerialNumber'))
    for meter in Meters:
        meterRead = djangoproject.models.ipbase.MeterRead.objects.filter(Meter=meter)
        if(not meterRead):
            continue #Append a note here that no readings were availble FIXME
        meterRead = meterRead[0]
        #Begin Hourly Read Section
        HourlyReads=meterRead.GetHourlyKWHNetDict()
        for date in RecentDates[1:]:  #Do not include the latest date (1st in the sorted list), as it may have a daily reading of 0, with the reverse rotations needed to get to the reading that did not come in yet to COOPIP
            if(not HourlyReads.has_key(date)):
                continue
            if('-') in str(HourlyReads[date]):  #Meaning a negative reading, reverse rotation, exists
                Row = []
                Row.append(meter.SerialNumber)
                Row.append(date)
                for hourlyreading in HourlyReads[date]:
                    if(len(str(hourlyreading)) > 6):
                        Row.append(str(hourlyreading)[:6])
                    else:
                        Row.append(str(hourlyreading))
                HourlyTableList.append(Row)
        #End Hourly Read Section           
        #Begin Daily Read Section
        DailyReads=meterRead.GetKWHUsageDict()
        FoundDailyReverseRotation = False
        Row=[]
        Row.append(meter.SerialNumber + ' ' + meter.RevenueProtectionReportNote)
        for date in RecentDates:
            if(not DailyReads.has_key(date)):
                Row.append('N/A')
                continue
            Row.append(str(DailyReads[date]))
            if('-') in str(DailyReads[date]):  #Meaning a negative reading, reverse rotation, exists
                FoundDailyReverseRotation = True
        if(FoundDailyReverseRotation):
            DailyTableList.append(Row)
        #End Daily Read Section
    HourlyTable = reportlab.platypus.Table(HourlyTableList)
    if(HourlyTableList):
        FontSizeStyleRow = ['FONTSIZE',         [1,1],      [-1, -1], 8]
        tableStyle = reportlab.platypus.TableStyle([FontSizeStyleRow])
        HourlyTable.setStyle(tableStyle)
    DailyTable = reportlab.platypus.Table(DailyTableList)
    return HourlyTable, DailyTable 
        
        
def CreateRecentDisconnectsFlowables(Debug):
    NumberOfDays = 14
    StartDate = datetime.datetime.now() - datetime.timedelta(days=NumberOfDays)
    RecentDates=djangoproject.models.ipbase.MeterBlink().GetMostRecentDates(NumberOfDates=NumberOfDays)[7:]
    CISRecentDates = []
    DateToDisconnectDateDict={}
    for date in RecentDates:  #Change The Date from YYYY-MM-DD format to YYMMDD format
        CISRecentDates.append(date[2:4] + date[5:7] + date[8:10])
        DateToDisconnectDateDict[date[2:4] + date[5:7] + date[8:10]] = date
    styles=reportlab.lib.styles.getSampleStyleSheet()
    HeadingList=[reportlab.platypus.Paragraph('Meter #', styles['Normal']), reportlab.platypus.Paragraph('Location', styles['Normal']), reportlab.platypus.Paragraph('Disconnect Date', styles['Normal'])]
    Results = []
    Results.append(HeadingList)
    Locations = CIS.Locationdetl.objects.filter(discdate__in=CISRecentDates).order_by('discdate')

    for location in Locations:
        Meters = CIS.Meterdetl.objects.filter(location=location.location)
        for meter in Meters:
            try:
                Note = djangoproject.models.ipbase.Meter.objects.filter(SerialNumber=meter.meter.lstrip('0'))[0].RevenueProtectionReportNote
            except IndexError:
                Note = ''
            if(not Note):
                Note = ''
            else:
                Note = ' ' + Note
            Results.append([str(meter.meter) + Note, meter.location, DateToDisconnectDateDict[location.discdate]])

    #Not Get Prepay disconnects

    Start = datetime.datetime.now() - datetime.timedelta(days=7)
    InactivePrepays = list(CIS.Ppmdetl.objects.filter(ppmstatus__istartswith='inactive'))
    for inactivePrepay in InactivePrepays:
        Ppmhistmstrs = list(CIS.Ppmhistmstr.objects.filter(mbrsep=inactivePrepay.mbrsep).order_by('-systemtime'))
        for ppmhistory in Ppmhistmstrs:
            BecomeInactiveDateTime=ppmhistory.systemtime
            if(not 'Inactive' in ppmhistory.ppmstatus):
                break
        if(BecomeInactiveDateTime < Start):
            meterdetl=CIS.Meterdetl.objects.filter(mbrsep=inactivePrepay)
            if(len(meterdetl) == 1):
                meter = meterdetl[0].meter
                location = meter.location
            else:
                meter = 'UNKNOWN ' + str(inactivePrepay.mbrsep)
                location = 'UNKNOWN'
            COOPIPMeter=djangoproject.models.ipbase.Meter.objects.filter(SerialNumber=meter)
            if(COOPIPMeter):
                Note = ' ' + COOPIPMeter[0].RevenueProtectionReportNote
            else:
                Note = ""
            Results.append([str(meter) + Note, location, str(BecomeInactiveDateTime)])

    return reportlab.platypus.Table(Results)


